const mysql = require('mysql2/promise');
const fs = require('fs');

async function executeCompatibleForeignKeys() {
  let connection;
  
  try {
    connection = await mysql.createConnection({
      host: '43.143.253.188',
      port: 3306,
      user: 'nextjs',
      password: 'yEAiRE2jcxTyRBy5',
      database: 'nextjs',
      multipleStatements: true
    });

    console.log('✅ 数据库连接成功');

    const sqlContent = fs.readFileSync('./fix-compatible-foreign-keys.sql', 'utf8');
    const statements = sqlContent
      .split('\n')
      .filter(line => line.trim() && !line.trim().startsWith('--'))
      .join('\n')
      .split(';')
      .filter(stmt => stmt.trim());

    console.log(`📝 准备执行 ${statements.length} 条SQL语句`);

    let successCount = 0;
    let errorCount = 0;

    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i].trim();
      if (!statement) continue;

      try {
        await connection.execute(statement);
        successCount++;
        console.log(`✅ 执行成功 (${i + 1}/${statements.length}): ${statement.substring(0, 50)}...`);
      } catch (error) {
        errorCount++;
        console.log(`❌ 执行失败 (${i + 1}/${statements.length}): ${error.message}`);
        console.log(`   语句: ${statement.substring(0, 100)}...`);
      }
    }

    console.log(`\n📊 执行结果统计:`);
    console.log(`   ✅ 成功: ${successCount} 条`);
    console.log(`   ❌ 失败: ${errorCount} 条`);

    // 验证外键约束创建结果
    const [constraints] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        CONSTRAINT_NAME,
        COLUMN_NAME,
        REFERENCED_TABLE_NAME,
        REFERENCED_COLUMN_NAME
      FROM information_schema.KEY_COLUMN_USAGE 
      WHERE TABLE_SCHEMA = 'nextjs' 
        AND REFERENCED_TABLE_NAME IS NOT NULL
      ORDER BY TABLE_NAME, CONSTRAINT_NAME
    `);

    console.log(`\n📋 当前共有 ${constraints.length} 个外键约束:`);
    constraints.forEach(constraint => {
      console.log(`   ${constraint.TABLE_NAME}.${constraint.COLUMN_NAME} -> ${constraint.REFERENCED_TABLE_NAME}.${constraint.REFERENCED_COLUMN_NAME}`);
    });

  } catch (error) {
    console.error('❌ 执行过程中发生错误:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

executeCompatibleForeignKeys();
